﻿using System.Collections.Generic;
using UnityEngine;
using MySql.Data.MySqlClient;
using System.Data;
using System.Net.Sockets;
using System.Threading;
using System;

/// <summary>
/// Mysql 数据库字段对
/// </summary>
[System.Serializable]
public class MysqlItemData
{
    public string columu;
    public string value;
    public override string ToString()
    {
        return columu + "\t" + value;
    }
}

/// <summary>
/// 数据库链接信息
/// </summary>
[System.Serializable]
public class MysqlInfo
{
    [Tooltip("网络地址")]
    public string ipAddress;
    [Tooltip("网络端口")]
    public int port;
    [Tooltip("数据库用户名")]
    public string user;
    [Tooltip("数据库密码")]
    public string password;
    [Tooltip("数据库库名")]
    public string databaseName;
}


public class MysqlHelper
{
    static MySqlConnection conn;
    [Tooltip("数据库连接信息字符串")]
    static string connectStr = "";
    [Tooltip("连接信息设置标识")]
    static bool isSettingConnectStr;

    /// <summary>
    /// 设置数据库连接信息
    /// </summary>
    /// <param name="mysqlInfo">数据库信息</param>
    public static void SetConnect(MysqlInfo mysqlInfo)
    {
        if (string.IsNullOrEmpty(mysqlInfo.ipAddress) ||
            string.IsNullOrEmpty(mysqlInfo.user) ||
            string.IsNullOrEmpty(mysqlInfo.password) ||
            string.IsNullOrEmpty(mysqlInfo.databaseName)
            )
            isSettingConnectStr = false;
        else
        {
            connectStr = string.Format("server={0};port={1};database={2};user={3};password={4};", mysqlInfo.ipAddress, mysqlInfo.port, mysqlInfo.databaseName, mysqlInfo.user, mysqlInfo.password);
            isSettingConnectStr = true;
        }
    }

    public static void ConnectMySql(MysqlInfo mysqlInfo)
    {
        SetConnect(mysqlInfo);
        if (!isSettingConnectStr)
        {
            Debug.LogError("数据库-连接参数错误");
            return;
        }
        try
        {
            conn = new MySqlConnection(connectStr);//还未与数据库建立连接
            conn.Open();//建立连接
            Debug.Log("数据库-连接成功：" + mysqlInfo.databaseName);
        }
        catch (Exception e)
        {
            Debug.LogError("数据库连接失败：" + e.Message);
        }
    }

    public static DataTable SelectSqlView<T>(string tableName, T judgeValues) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return new DataTable();
        try//捕捉异常，并打印
        {
            string judgeValue = ""; string sql = "";
            for (int i = 0; i < judgeValues.Count; i++)
            {
                judgeValue += judgeValues[i].columu + "='" + judgeValues[i].value + "'" + (i < (judgeValues.Count - 1) ? " AND " : "");
            }
            if (string.IsNullOrEmpty(judgeValue))
                sql = "SELECT * FROM `" + tableName + "`";//数据库操作命令,查询数据
            else
                sql = "SELECT * FROM `" + tableName + "` WHERE " + judgeValue;//数据库操作命令,插入数据
            //Debug.Log(sql);                                                                               
            MySqlDataAdapter DA = new MySqlDataAdapter(sql, conn);// 数据库命令类
            DataTable dt = new DataTable();
            DA.Fill(dt);
            return dt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return new DataTable();
        }
    }

    #region 初代

    /// <summary>
    /// 封装测试-数据库更新操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="tableName">表名</param>
    /// <param name="newValues">新值</param>
    /// <param name="oldValues">替换条件</param>
    /// <returns></returns>
    public static int Update<T>(string tableName, T newValues, T oldValues) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string newValue = "";
            for (int i = 0; i < newValues.Count; i++)
            {
                newValue += newValues[i].columu + "='" + newValues[i].value + "'" + (i < (newValues.Count - 1) ? "," : "");
            }
            string oldValue = "";
            for (int i = 0; i < oldValues.Count; i++)
            {
                oldValue += oldValues[i].columu + "='" + oldValues[i].value + "'" + (i < (oldValues.Count - 1) ? " AND " : "");
            }
            string sql = "UPDATE `" + tableName + "` SET " + newValue + " WHERE " + oldValue;//数据库操作命令,插入数据
            //Debug.Log(sql);
            MySqlCommand cmd = new MySqlCommand(sql, conn);//数据库命令类
            cmd.CommandTimeout = 1200;
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            //Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", sql, resurt));
            //ThreadStart();
            return resurt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    /// <summary>
    /// 同时更新多表数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="tableName"></param>
    /// <param name="newValues"></param>
    /// <param name="oldValues"></param>
    /// <returns></returns>
    public static int Updates<T>(List<string> tableName, T newValues, T oldValues) where T : List<MysqlItemData>
    {
        List<string> isOnList = GetIsOn(tableName, oldValues);
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string tableStr = "";
            string newValue = "";
            string oldValue = "";
            for (int i = 0; i < isOnList.Count; i++)
            {
                tableStr += "`" + isOnList[i] + (i < (isOnList.Count - 1) ? "`," : "`");
                newValue += "`" + isOnList[i] + "`.`" + newValues[0].columu + "`='" + newValues[0].value + "'" + (i < (isOnList.Count - 1) ? "," : "");
                oldValue += "`" + isOnList[i] + "`.`" + oldValues[0].columu + "`='" + oldValues[0].value + "'" + (i < (isOnList.Count - 1) ? "," : "");
            }

            //for (int i = 0; i < newValues.Count; i++)
            //{
            //    newValue += newValues[i].columu + "='" + newValues[i].value + "'" + (i < (newValues.Count - 1) ? "," : "");
            //}
            //for (int i = 0; i < oldValues.Count; i++)
            //{
            //    oldValue += oldValues[i].columu + "='" + oldValues[i].value + "'" + (i < (oldValues.Count - 1) ? " AND " : "");
            //} 
            string sql = "UPDATE " + tableStr + "SET " + newValue + " WHERE " + oldValue;//数据库操作命令,插入数据
            Debug.Log(sql);
            MySqlCommand cmd = new MySqlCommand(sql, conn);//数据库命令类
            cmd.CommandTimeout = 1200;
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            //Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", sql, resurt));
            //ThreadStart();
            return resurt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    public static List<string> GetIsOn(List<string> tableName, List<MysqlItemData> mysqlItemDatas)
    {
        isOn = true;
        for (int i = tableName.Count - 1; i >= 0; i--)
        {
            if (Select1(tableName[i], mysqlItemDatas, i == tableName.Count - 1).Rows.Count <= 0)
            {
                tableName.RemoveAt(i);
            }
        }
        return tableName;
    }

    static bool isOn;
    public static DataTable Select1<T>(string tableName, T judgeValues, bool isOn1) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return new DataTable();
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            if (isOn)
            {
                conn.Open();//建立连接
                isOn = false;
            }

            string judgeValue = ""; string sql = "";
            for (int i = 0; i < judgeValues.Count; i++)
            {
                judgeValue += judgeValues[i].columu + "='" + judgeValues[i].value + "'" + (i < (judgeValues.Count - 1) ? " AND " : "");
            }
            if (string.IsNullOrEmpty(judgeValue))
                sql = "SELECT 1 FROM `" + tableName + "`";
            else
                sql = "SELECT 1 FROM `" + tableName + "` WHERE " + judgeValue;
            //Debug.Log(sql);                                                                               
            MySqlDataAdapter DA = new MySqlDataAdapter(sql, conn);// 数据库命令类
            DataTable dt = new DataTable();
            DA.Fill(dt);
            return dt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return new DataTable();
        }
        finally//无论如何都会去执行的语句
        {
            if (isOn1)
            {
                conn.Close();//关闭连接
            }
        }
    }

    /// <summary>
    /// 封装测试-数据库更新操作
    /// </summary>
    ///  <param name="tableName">表名</param>
    ///  <param name="newValues">新值</param>
    ///  <param name="oldValues">替换条件</param>
    /// <returns></returns>
    public static int Update<T>(string tableName, T[] newValues, T[] oldValues) where T : MysqlItemData
    {
        return Update(tableName, new List<MysqlItemData>(newValues), new List<MysqlItemData>(oldValues));
    }

    /// <summary>
    /// 封装测试-数据库删除操作
    /// </summary>
    /// <typeparam name="T">泛型 T</typeparam>
    /// <param name="tableName">表名</param>
    /// <param name="judgeValues">判断条件</param>
    /// <returns></returns>
    public static int Delete<T>(string tableName, T judgeValues) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string judgeValue = "";
            for (int i = 0; i < judgeValues.Count; i++)
            {
                judgeValue += judgeValues[i].columu + "='" + judgeValues[i].value + "'" + (i < (judgeValues.Count - 1) ? " AND " : "");
            }
            string sql = "DELETE FROM `" + tableName + "` WHERE " + judgeValue;//数据库操作命令,插入数据                                                                               
            MySqlCommand cmd = new MySqlCommand(sql, conn);//数据库命令类
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            //ThreadStart();
            Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", sql, resurt));
            return resurt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    public static int Deletes<T>(List<string> tableName, T judgeValues) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        GetIsOn(tableName, judgeValues);

        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string sqlTableStr = "";
            string judgeValue = "";
            for (int i = 0; i < tableName.Count; i++)
            {
                sqlTableStr += "`" + tableName[i] + (i < (tableName.Count - 1) ? "`," : "`");
                judgeValue += "`" + tableName[i] + "`.`" + judgeValues[0].columu + "`='" + judgeValues[0].value + "'" + (i < (tableName.Count - 1) ? " AND " : "");
            }

            string sql = "DELETE" + sqlTableStr + "FROM " + sqlTableStr + " WHERE " + judgeValue;//数据库操作命令,插入数据
            Debug.Log(sql);

            MySqlCommand cmd = new MySqlCommand(sql, conn);//数据库命令类
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            //ThreadStart();
            //Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", sql, resurt));
            return resurt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    /// <summary>
    /// 封装测试-数据库删除操作
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="judgeValues">判断条件</param>
    /// <returns></returns>
    public static int Delete<T>(string tableName, T[] judgeValues) where T : MysqlItemData
    {
        return Delete(tableName, new List<MysqlItemData>(judgeValues));
    }

    /// <summary>
    /// 封装测试-数据库插入操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="tableName">表名</param>
    /// <param name="insertDatas">将要插入的数据</param>
    /// <returns></returns>
    public static int Insert<T>(string tableName, T insertDatas) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接
            string columu = "";
            string value = "";
            for (int i = 0; i < insertDatas.Count; i++)
            {
                columu += insertDatas[i].columu + (i < (insertDatas.Count - 1) ? "," : "");
                value += "'" + insertDatas[i].value + "'" + (i < (insertDatas.Count - 1) ? "," : "");
            }
            string sql = "INSERT INTO `" + tableName + "` (" + columu + ") VALUES (" + value + ")";//数据库操作命令,插入数据
            Debug.Log(string.Format("QUERY：{0}", sql));
            MySqlCommand cmd = new MySqlCommand(sql, conn);//数据库命令类
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", sql, resurt));
            //ThreadStart();
            return resurt;
        }
        catch (System.Exception e)
        {
            conn.Close();//关闭连接
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }
    /// <summary>
    /// 封装测试-数据库插入操作
    /// </summary>
    /// <param name="tableName">表名</param>
    /// <param name="insertDatas">将要插入的数据</param>
    /// <returns></returns>
    public static int Insert<T>(string tableName, T[] insertDatas) where T : MysqlItemData
    {
        return Insert(tableName, new List<MysqlItemData>(insertDatas));
    }

    /// <summary>
    /// Mysql 数据库查询操作
    /// </summary>
    /// <typeparam name="T">泛型 T</typeparam>
    /// <param name="tableName">查询表名</param>
    /// <param name="judgeValues">查询判断条件</param>
    /// <returns></returns>
    public static DataTable Select<T>(string tableName, T judgeValues) where T : List<MysqlItemData>
    {
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return new DataTable();
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string judgeValue = ""; string sql = "";
            for (int i = 0; i < judgeValues.Count; i++)
            {
                judgeValue += judgeValues[i].columu + "='" + judgeValues[i].value + "'" + (i < (judgeValues.Count - 1) ? " AND " : "");
            }
            if (string.IsNullOrEmpty(judgeValue))
                sql = "SELECT * FROM `" + tableName + "`";//数据库操作命令,查询数据
            else
                sql = "SELECT * FROM `" + tableName + "` WHERE " + judgeValue;//数据库操作命令,插入数据
            //Debug.Log(sql);                                                                               
            MySqlDataAdapter DA = new MySqlDataAdapter(sql, conn);// 数据库命令类
            DataTable dt = new DataTable();
            DA.Fill(dt);
            return dt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return new DataTable();
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    public static DataTable SelectNotNull(string tableName, string columuName)
    {
        //if (string.IsNullOrEmpty(connectStr))
        //{
        //    MysqlHelper.SetConnect(mysqlInfo);
        //}
        //string connectStr = "server=120.25.222.26;port=3306;database=chelafuclientdatabace;user=root;password=123456;";//sql连接数据参数
        if (!isSettingConnectStr) return new DataTable();
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接

            string sql = "";

            sql = "SELECT * FROM `" + tableName + "` WHERE `" + columuName+ "` is not null";//数据库操作命令,插入数据
            //Debug.Log("非空查询"+sql);
            MySqlDataAdapter DA = new MySqlDataAdapter(sql, conn);// 数据库命令类
            DataTable dt = new DataTable();
            DA.Fill(dt);
            return dt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return new DataTable();
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    static Thread thread;
    static void ThreadStart()
    {
        thread = new Thread(UpdateSelect);
        thread.IsBackground = true;
        thread.Start();
    }

    static void UpdateSelect()
    {
        //if (MysqlManager.Instance)
        //{
        //    MysqlManager.Instance.SchoolDatas = Select(MysqlManager.Instance.schoolManagerDatatableName, new List<MysqlItemData>());
        //    MysqlManager.Instance.UserDatas = Select(MysqlManager.Instance.userDatatableName, new List<MysqlItemData>());
        //    MysqlManager.Instance.CourseData = Select(MysqlManager.Instance.courseNameDatatableName, new List<MysqlItemData>());
        //    MysqlManager.Instance.ExamResultData = Select(MysqlManager.Instance.examTableName, new List<MysqlItemData>());
        //}
    }

    /// <summary>
    /// 修改数据库
    /// </summary>
    /// <param name="queryStr">Mysql 数据库执行语句</param>
    /// <returns></returns>
    public static int AlterMysql(string queryStr)
    {
        if (!isSettingConnectStr) return -1;
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接
            MySqlCommand cmd = new MySqlCommand(queryStr, conn);//数据库命令类
            int resurt = cmd.ExecuteNonQuery();//返回值是数据库受影响行数的记录
            Debug.Log(string.Format("QUERY：{0}\nRESURT：{1}", queryStr, resurt));
            //ThreadStart();
            return resurt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return -1;
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }
    /// <summary>
    /// 查询数据库
    /// </summary>
    /// <param name="queryStr">Mysql 数据库执行语句</param>
    /// <returns></returns>
    public static DataTable QueryMysql(string queryStr)
    {
        if (!isSettingConnectStr) return new DataTable();
        MySqlConnection conn = new MySqlConnection(connectStr);//还未与数据库建立连接
        try//捕捉异常，并打印
        {
            conn.Open();//建立连接
            MySqlDataAdapter DA = new MySqlDataAdapter(queryStr, conn);// 数据库命令类
            DataTable dt = new DataTable();
            DA.Fill(dt);
            //ThreadStart();
            return dt;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return new DataTable();
        }
        finally//无论如何都会去执行的语句
        {
            conn.Close();//关闭连接
        }
    }

    /// <summary> 
    /// 采用Socket方式，测试服务器连接 
    /// </summary> 
    /// <param name="host">服务器主机名或IP</param> 
    /// <param name="port">端口号</param> 
    /// <param name="millisecondsTimeout">等待时间：毫秒</param> 
    /// <returns></returns> 
    public static bool TestConnection(string host, int port, int millisecondsTimeout)
    {
        TcpClient client = new TcpClient();
        try
        {
            client.Connect(host, port);
            //ar.AsyncWaitHandle.WaitOne(millisecondsTimeout);
            return client.Connected;
        }
        catch (System.Exception e)
        {
            Debug.LogError(e.Message);
            return false;
        }
        finally
        {
            client.Close();
        }
    }


    /// <summary>
    /// 查询datatable表中数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dt"></param>
    /// <param name="judge"></param>
    /// <returns></returns>
    public static DataRow[] DataTableSelect<T>(DataTable dt, T judges) where T : List<MysqlItemData>
    {
        if (judges.Count <= 0) return new DataRow[0];
        string sql = "";
        sql += judges[0].columu + " = '" + judges[0].value + "'";
        for (int i = 1; i < judges.Count; i++)
        {
            sql += " AND " + judges[i].columu + " = '" + judges[i].value + "'";
        }
        //Debug.Log(sql);
        return dt.Select(sql);
    }
    /// <summary>
    /// 查询datatable表中数据
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="columu"></param>
    /// <param name="value"></param>
    /// <returns></returns>
    public static DataRow[] DataTableSelect(DataTable dt, string columu, string value)
    {
        return dt.Select(columu + " = '" + value + "'");
    }
    public static DataRow[] DataTableSelect(DataTable dt, string[] columus, string[] values)
    {
        if (columus.Length != values.Length || columus.Length <= 0 || values.Length <= 0) return new DataRow[0];
        string sql = "";
        sql += columus[0] + " = '" + values[0] + "'";
        for (int i = 1; i < columus.Length; i++)
        {
            sql += " AND " + columus[i] + " = '" + values[i] + "'";
        }
        return dt.Select(sql);
    }
    #endregion
}
